package com.example.demo.dao;

import com.example.demo.entity.CartVo;
import com.example.demo.entity.UserCartVo;

import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;
public class CartDao {

    public CartVo findByCartID(Integer id) throws Exception{
        CartVo c = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            Class.forName(  DB.JDBC_DRIVER);
            con = java.sql.DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps = con.prepareStatement("select * from  Cart where CartID = ?");
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while(rs.next()){
                c = new CartVo();
                c.setCartId(rs.getInt("CartID"));
                c.setUserId(rs.getInt("UserID"));
                c.setBookId(rs.getInt("BookID"));
                c.setQuantity(rs.getInt("Quantity"));
            }
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("查询失败");
        }finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
        return c;
    }



   public void saveCart(CartVo c) throws Exception{
        Connection con = null;
        PreparedStatement ps = null;
        try{
            Class.forName(DB.JDBC_DRIVER);
            con = java.sql.DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps = con.prepareStatement("insert into Cart(UserID,BookID,Quantity) values(?,?,?)");
            ps.setInt(1,c.getUserId());
            ps.setInt(2,c.getBookId());
            ps.setInt(3,c.getQuantity());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("保存失败");
        }finally {
            if (ps != null) ps.close();
            if (con != null) con.close();

        }
   }

   public void updateCart(CartVo c) throws Exception{
        Connection con = null;
        PreparedStatement ps = null;
        try{
            Class.forName(DB.JDBC_DRIVER);
            con = java.sql.DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps = con.prepareStatement("update Cart set BookID=?, Quantity = ? where CartID = ?");
            ps.setInt(1,c.getBookId());
            ps.setInt(2,c.getQuantity());
            ps.setInt(3,c.getCartId());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("更新失败");
        }finally {
            if (ps != null) ps.close();
            if (con != null) con.close();

        }
   }

   public void deleteCartById(Integer id) throws Exception{
        Connection con = null;
        PreparedStatement ps = null;
        try{
            Class.forName(DB.JDBC_DRIVER);
            con = java.sql.DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps = con.prepareStatement("delete from Cart where CartID = ?");
            ps.setInt(1,id);
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("删除失败");
        }finally {
            if (ps != null) ps.close();
            if (con != null) con.close();

        }
   }
    public List<UserCartVo> getCartByUserid(Integer id) throws Exception{
        List<UserCartVo> list = new ArrayList<>();
        UserCartVo c = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            Class.forName(  DB.JDBC_DRIVER);
            con = java.sql.DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps = con.prepareStatement( "SELECT Cart.CartID, Book.Title, Book.Price, Cart.Quantity " +
                    "FROM Cart JOIN Book ON Cart.BookID = Book.BookID JOIN User ON Cart.UserID = User.UserID " +
                    "WHERE User.UserID = ?;");
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while(rs.next()){
                c = new UserCartVo();
                c.setId(rs.getInt("CartID"));
                c.setTitle(rs.getString("Title"));
                c.setPrice(rs.getDouble("Price"));
                c.setCount(rs.getInt("Quantity"));
                list.add(c);
            }
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("查询失败");
        }finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
        return list;
    }
}
